# 导入所需的库
import sqlite3  # 用于本地 SQLite 数据库操作
from flask import Flask, render_template, request, jsonify, redirect, url_for  # Flask 相关功能
import json  # JSON 数据处理（虽然当前代码中未直接使用）
import requests  # 用于发送外部 HTTP 请求，比如调用 AI 接口

# 创建 Flask 应用实例
app = Flask(__name__)

# 初始化数据库函数：创建表并插入初始数据
def init_db():
    # 连接到 SQLite 数据库文件 'hospital_knowledge.db'
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()

    # 创建知识库表，存储常见问题和答案
    c.execute('''CREATE TABLE IF NOT EXISTS knowledge_base
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 question TEXT NOT NULL,
                 answer TEXT NOT NULL,
                 category TEXT NOT NULL)''')

    # 创建用户咨询记录表，记录用户的提问及回答
    c.execute('''CREATE TABLE IF NOT EXISTS consultation_records
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 user_id TEXT,
                 question TEXT NOT NULL,
                 answer TEXT NOT NULL,
                 timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)''')

    # 创建科室信息表，存储医院各科室的信息
    c.execute('''CREATE TABLE IF NOT EXISTS departments
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 name TEXT NOT NULL,
                 description TEXT NOT NULL,
                 location TEXT NOT NULL,
                 contact TEXT NOT NULL)''')

    # 创建医生信息表，存储医生的详细信息，并关联科室
    c.execute('''CREATE TABLE IF NOT EXISTS doctors
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 name TEXT NOT NULL,
                 department_id INTEGER,
                 title TEXT NOT NULL,
                 specialty TEXT NOT NULL,
                 schedule TEXT NOT NULL,
                 FOREIGN KEY(department_id) REFERENCES departments(id))''')

    # 插入初始知识库数据（仅当表为空时执行）
    c.execute("SELECT COUNT(*) FROM knowledge_base")
    if c.fetchone()[0] == 0:
        initial_data = [
            ("医院上班时间", "本院门诊时间为周一至周日 8:00-17:30，急诊24小时开放。", "基本信息"),
            ("如何预约挂号",
             "您可以通过以下方式预约挂号：1. 医院官网在线预约 2. 微信公众号预约 3. 电话预约(123456789) 4. 现场挂号",
             "挂号服务"),
            ("医保报销比例", "本院医保报销比例为：在职职工80%，退休人员85%，城乡居民医保60%。具体以医保政策为准。",
             "医保政策"),
            ("体检需要空腹吗", "大部分体检项目需要空腹8-12小时，建议前一天晚上10点后禁食，早晨不要吃早餐。", "体检服务"),
            ("儿科在哪里", "儿科门诊位于门诊大楼3楼东侧，急诊儿科在急诊楼1楼。", "科室位置")
        ]
        c.executemany("INSERT INTO knowledge_base (question, answer, category) VALUES (?, ?, ?)", initial_data)

    # 插入初始科室信息（仅当表为空时执行）
    c.execute("SELECT COUNT(*) FROM departments")
    if c.fetchone()[0] == 0:
        departments = [
            ("内科", "负责各种内科疾病的诊断和治疗", "门诊大楼2楼", "023-12345671"),
            ("外科", "负责各种外科手术和创伤治疗", "门诊大楼2楼", "023-12345672"),
            ("儿科", "负责0-18岁儿童疾病的诊断和治疗", "门诊大楼3楼", "023-12345673"),
            ("妇产科", "负责妇科和产科疾病的诊断和治疗", "门诊大楼3楼", "023-12345674"),
            ("眼科", "负责眼部疾病的诊断和治疗", "门诊大楼4楼", "023-12345675")
        ]
        c.executemany("INSERT INTO departments (name, description, location, contact) VALUES (?, ?, ?, ?)", departments)

    # 插入初始医生信息（仅当表为空时执行）
    c.execute("SELECT COUNT(*) FROM doctors")
    if c.fetchone()[0] == 0:
        doctors = [
            ("张医生", 1, "主任医师", "心血管疾病", "周一、三、五上午"),
            ("李医生", 2, "副主任医师", "普外科手术", "周二、四全天，周六上午"),
            ("王医生", 3, "主任医师", "儿科常见病", "周一至周五全天"),
            ("赵医生", 4, "副主任医师", "妇科肿瘤", "周三、五全天，周日上午"),
            ("刘医生", 5, "主治医师", "白内障手术", "周一、二、四全天")
        ]
        c.executemany("INSERT INTO doctors (name, department_id, title, specialty, schedule) VALUES (?, ?, ?, ?, ?)",
                      doctors)

    conn.commit()
    conn.close()


# 调用初始化数据库函数
init_db()

# 智能问答接口配置
API_URL = "https://open.bigmodel.cn/api/paas/v4/chat/completions"
API_KEY = "2e912c6763e743c098c2d6f3b2040c0d.DPeEQfjzOj0YCpCn"  # 替换为你的实际 API 密钥

headers = {
    "Authorization": API_KEY,
    "Content-Type": "application/json"
}

# 首页路由
@app.route('/')
def index():
    return render_template('index.html')  # 返回首页模板

# 智能问答 API
@app.route('/ask', methods=['POST'])
def ask_question():
    user_question = request.form.get('question')  # 获取用户输入的问题
    user_id = request.form.get('user_id', 'anonymous')  # 获取用户 ID，如果不存在则设为 anonymous

    # 连接数据库，尝试从知识库查找匹配答案
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    c.execute("SELECT answer FROM knowledge_base WHERE question LIKE ?", ('%' + user_question + '%',))
    result = c.fetchone()

    if result:
        answer = result[0]
        source = "知识库"  # 来自知识库的回答
    else:
        # 如果知识库无结果，则调用 AI API 获取回答
        payload = {
            "model": "GLM-4",
            "messages": [{"role": "user", "content": user_question}],
            "temperature": 0.7,
            "max_tokens": 500
        }

        try:
            response = requests.post(API_URL, headers=headers, json=payload)
            response_data = response.json()
            answer = response_data['choices'][0]['message']['content']
            source = "智能小医生"  # 来自 AI 的回答
        except Exception as e:
            answer = "抱歉，我暂时无法回答这个问题。您可以尝试联系医院客服热线：123456789。"
            source = "系统"  # 发生异常时的备用回答

    # 将本次咨询记录写入数据库
    c.execute("INSERT INTO consultation_records (user_id, question, answer) VALUES (?, ?, ?)",
              (user_id, user_question, answer))
    conn.commit()
    conn.close()

    return jsonify({
        "question": user_question,
        "answer": answer,
        "source": source
    })

# 知识库管理页面
@app.route('/knowledge')
def knowledge_management():
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    c.execute("SELECT * FROM knowledge_base")
    knowledge_items = c.fetchall()  # 获取所有知识库条目
    conn.close()
    return render_template('knowledge.html', knowledge_items=knowledge_items)  # 显示知识库列表

# 添加知识库条目
@app.route('/add_knowledge', methods=['POST'])
def add_knowledge():
    question = request.form.get('question')  # 获取新问题
    answer = request.form.get('answer')      # 获取对应答案
    category = request.form.get('category')  # 获取分类

    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    c.execute("INSERT INTO knowledge_base (question, answer, category) VALUES (?, ?, ?)",
              (question, answer, category))  # 插入新条目
    conn.commit()
    conn.close()
    return redirect(url_for('knowledge_management'))  # 返回知识库管理页面

# 删除知识库条目
@app.route('/delete_knowledge/<int:id>')
def delete_knowledge(id):
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    c.execute("DELETE FROM knowledge_base WHERE id=?", (id,))  # 删除指定 ID 的条目
    conn.commit()
    conn.close()
    return redirect(url_for('knowledge_management'))  # 返回知识库管理页面

# 科室信息展示
@app.route('/departments')
def department_list():
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    c.execute("SELECT * FROM departments")  # 查询所有科室信息
    departments = c.fetchall()
    conn.close()
    return render_template('departments.html', departments=departments)  # 显示科室列表

# 医生信息展示
@app.route('/doctors')
def doctor_list():
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    # 查询医生信息，包括所属科室名称
    c.execute('''SELECT doctors.id, doctors.name, departments.name as department, 
                 doctors.title, doctors.specialty, doctors.schedule 
                 FROM doctors JOIN departments ON doctors.department_id = departments.id''')
    doctors = c.fetchall()
    conn.close()
    return render_template('doctors.html', doctors=doctors)  # 显示医生列表

# 咨询记录展示
@app.route('/records')
def consultation_records():
    conn = sqlite3.connect('hospital_knowledge.db')
    c = conn.cursor()
    # 使用strftime函数将时间转换为本地时间
    c.execute('''SELECT id, user_id, question, answer, 
                strftime('%Y-%m-%d %H:%M:%S', timestamp, 'localtime') as local_timestamp 
                FROM consultation_records ORDER BY timestamp DESC''')
    records = c.fetchall()
    conn.close()
    return render_template('records.html', records=records)

# 启动 Flask 应用
if __name__ == '__main__':
    app.run(debug=True)  # 开启调试模式